package com.bestv.search.common.dao.hibernate;

import com.bestv.search.common.dao.PersonDao;
import com.bestv.search.common.model.Person;
import com.bestv.search.common.model.PersonExtend;
import com.bestv.search.common.model.PersonProduct;
import com.bestv.search.common.util.StringConst;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class PersonDaoHibernate extends GenericDaoHibernate<Person, Long> implements PersonDao {

    public PersonDaoHibernate() {
        super(Person.class);
    }

    @Override
    public List<String> getAllName(int start, int limit) {
        String sql = "select distinct name from person where description is not null";
        Session session = null;
        List<String> result = new ArrayList<String>();
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            query.setFirstResult(start);
            query.setMaxResults(limit);
            List<?> list = query.list();
            if (list != null && list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    @SuppressWarnings("rawtypes")
                    Map map = (Map) list.get(i);
                    String name = (String) map.get("name");
                    result.add(name);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return result;
    }

    @Override
    public int getCount() {
        String sql = "select count(*) as amount from person";
        Session session = null;

        int count = 0;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            List<?> list = query.list();
            if (list != null && list.size() > 0) {
                if (list.get(0) != null) {
                    count = Integer.valueOf(list.get(0).toString());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return count;
    }

    @Override
    public void saveProductInfoReference(String personProductId, long info_id) {
        String sql = "insert into info_personProduct(personProduct_id, info_id) values(?, ?)";
        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            query.setString(0, personProductId);
            query.setLong(1, info_id);
            query.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
    }

    @Override
    public boolean existProductInfoReference(String personProductId, long info_id) {
        String sql = "select * from info_personProduct where personProduct_id = ? and info_id = ?";
        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            query.setString(0, personProductId);
            query.setLong(1, info_id);
            List<?> list = query.list();
            return list == null || list.size() == 0 ? false : true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return false;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> getPersonIdByInfoId(long info_id) {
        String sql = "select distinct personProduct.personID from info_personProduct inner join personProduct"
                + " on info_personProduct.personProduct_id = personProduct.id where info_personProduct.info_id=?";
        Session session = null;
        List<String> ids = new ArrayList<String>();
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            query.setLong(0, info_id);
            List<Map<String, Object>> result = query.list();
            if (result != null && result.size() > 0) {
                for (int i = 0; i < result.size(); i++) {
                    Map<String, Object> map = result.get(i);
                    Object personId = map.get("personID");
                    if (personId != null) {
                        ids.add(personId.toString());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return ids;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<PersonExtend> getPersonByUpdateTime(Date updateTime, int start, int limit) {
        String sql = "select distinct person.personId, person.name, person.avatar_iqiyi, person.avatar_youku, personExtend.avatar, person.gender, person.nationality,"
                + " person.bloodtype, person.constellation, person.occupation, person.height, person.birthday, person.alias, person.homeplace,"
                + " person.description, person.honor, person.updateTime, personExtend.index_flag from person left join personExtend on personExtend.id = person.id"
                + " where person.updateTime>? order by person.updateTime asc";

        Session session = null;
        List<PersonExtend> persons = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("personId", Hibernate.STRING)
                    .addScalar("name", Hibernate.STRING).addScalar("avatar_iqiyi", Hibernate.BLOB)
                    .addScalar("avatar_youku", Hibernate.BLOB).addScalar("avatar", Hibernate.BLOB)
                    .addScalar("gender", Hibernate.STRING).addScalar("nationality", Hibernate.STRING)
                    .addScalar("bloodtype", Hibernate.STRING).addScalar("constellation", Hibernate.STRING)
                    .addScalar("occupation", Hibernate.STRING).addScalar("height", Hibernate.STRING)
                    .addScalar("birthday", Hibernate.STRING).addScalar("alias", Hibernate.STRING)
                    .addScalar("homeplace", Hibernate.STRING).addScalar("description", Hibernate.STRING)
                    .addScalar("honor", Hibernate.STRING).addScalar("updateTime", Hibernate.TIMESTAMP)
                    .addScalar("index_flag", Hibernate.INTEGER)
                    .setResultTransformer(Transformers.aliasToBean(PersonExtend.class));
            query.setTimestamp(0, updateTime);
            query.setFirstResult(start);
            query.setMaxResults(limit);
            persons = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return persons;
    }

    @Override
    public int getPersonCountByUpdateTime(Date updateTime) {
        String sql = "select count(distinct person.personId) as amount from person where person.updateTime>?";
        Session session = null;

        int count = 0;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            query.setTimestamp(0, updateTime);
            List<?> list = query.list();
            if (list != null && list.size() > 0) {
                if (list.get(0) != null) {
                    count = Integer.valueOf(list.get(0).toString());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return count;
    }

    @SuppressWarnings("unchecked")
    @Override
    public PersonExtend getPersonById(String personId) {
        String sql = "select distinct person.personId, person.name, person.avatar_iqiyi, person.avatar_youku, personExtend.avatar, person.gender, person.nationality,"
                + " person.bloodtype, person.constellation, person.occupation, person.height, person.birthday, person.alias, person.homeplace,"
                + " person.description, person.honor, person.updateTime, personExtend.index_flag from person left join personExtend on personExtend.id = person.id"
                + " where person.personId=?";

        Session session = null;
        PersonExtend person = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("personId", Hibernate.STRING)
                    .addScalar("name", Hibernate.STRING).addScalar("avatar_iqiyi", Hibernate.BLOB)
                    .addScalar("avatar_youku", Hibernate.BLOB).addScalar("avatar", Hibernate.BLOB)
                    .addScalar("gender", Hibernate.STRING).addScalar("nationality", Hibernate.STRING)
                    .addScalar("bloodtype", Hibernate.STRING).addScalar("constellation", Hibernate.STRING)
                    .addScalar("occupation", Hibernate.STRING).addScalar("height", Hibernate.STRING)
                    .addScalar("birthday", Hibernate.STRING).addScalar("alias", Hibernate.STRING)
                    .addScalar("homeplace", Hibernate.STRING).addScalar("description", Hibernate.STRING)
                    .addScalar("honor", Hibernate.STRING).addScalar("updateTime", Hibernate.TIMESTAMP)
                    .addScalar("index_flag", Hibernate.INTEGER)
                    .setResultTransformer(Transformers.aliasToBean(PersonExtend.class));
            query.setString(0, personId);
            List<PersonExtend> list = query.list();
            if (list != null && list.size() > 0) {
                person = list.get(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return person;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<PersonProduct> getPersonProductsByPersonId(String personId) {
        String sql = "select personProduct.name as pName,  personProduct.category as pCategory, personProduct.year as pYear,"
                + " personProduct.description as pDescription , info.name as iName, info.code as iCode from personProduct left join"
                + " info_personProduct  on info_personProduct.personProduct_id = personProduct.id left join info on  info.id = info_personProduct.info_id"
                + " where personProduct.personId=? and (update_flag<2 or update_flag is null) order by personProduct.year desc";
        Session session = null;
        List<PersonProduct> personProducts = new ArrayList<PersonProduct>();
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            query.setString(0, personId);
            List<Map<String, Object>> result = query.list();
            if (result != null && result.size() > 0) {
                for (int i = 0; i < result.size(); i++) {
                    PersonProduct product = new PersonProduct();

                    // Gets product
                    Map<String, Object> map = result.get(i);

                    Object pName = map.get("pName");
                    if (pName != null) {
                        product.setName(pName.toString());
                    }

                    Object pCategory = map.get("pCategory");
                    if (pCategory != null) {
                        product.setCategory(pCategory.toString());
                    }

                    Object pYear = map.get("pYear");
                    if (pYear != null) {
                        product.setYear(Integer.valueOf(pYear.toString()));
                    }

                    Object pDescription = map.get("pDescription");
                    if (pDescription != null) {
                        product.setDescription(pDescription.toString());
                    }

                    Object iCode = map.get("iCode");
                    boolean exist = false;
                    if (iCode != null && !StringConst.EMPTY.equals(iCode)) {
                        for (PersonProduct p : personProducts) {
                            // product has exist in list
                            if (p.getName().equals(product.getName()) && p.getCategory().equals(product.getCategory())
                                    && p.getYear() == product.getYear()) {
                                product = p;
                                exist = true;
                                break;
                            }
                        }
                        product.getOnlineInfos().put(iCode.toString(),
                                map.get("iName") == null ? pName.toString() : map.get("iName").toString());
                    }

                    if (!exist) {
                        personProducts.add(product);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return personProducts;
    }

    @Override
    public void updateAvatar(String personId, byte[] avatar) {
        long id = getIdByPersonId(personId);
        if (!existExtendProprities(id)) {
            insertExtendProprities(id);
        }
        String hql = "update PersonExtend set avatar=?, updateTime=? where personId=?";
        Object[] paras = new Object[]{Hibernate.createBlob(avatar), new Date(), personId};
        this.getHibernateTemplate().bulkUpdate(hql, paras);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<PersonExtend> getPersonByName(String name, String[] nationalityArray, int start, int limit) {
        StringBuilder sql = new StringBuilder(
                "select distinct person.personId, person.name, person.avatar_iqiyi, person.avatar_youku, personExtend.avatar, person.gender, person.nationality,"
                        + " person.bloodtype, person.constellation, person.occupation, person.height, person.birthday, person.alias, person.homeplace,"
                        + " person.description, person.honor, person.updateTime, personExtend.index_flag from person left join personExtend on personExtend.id = person.id"
                        + " where name like ?");

        // nations
        if (nationalityArray != null && nationalityArray.length > 0) {
            StringBuilder sb = new StringBuilder();
            sb.append(" and nationality in (");
            for (int i = 0; i < nationalityArray.length; i++) {
                String nation = nationalityArray[i];
                sb.append("'");
                sb.append(nation);
                sb.append("'");
                if (i < nationalityArray.length - 1) {
                    sb.append(",");
                }
            }
            sb.append(")");
            sql.append(sb.toString());
        }

        Session session = null;
        List<PersonExtend> list = new ArrayList<PersonExtend>();

        try {
            session = getSession();
            Query query = session.createSQLQuery(sql.toString()).addScalar("personId", Hibernate.STRING)
                    .addScalar("name", Hibernate.STRING).addScalar("avatar_iqiyi", Hibernate.BLOB)
                    .addScalar("avatar_youku", Hibernate.BLOB).addScalar("avatar", Hibernate.BLOB)
                    .addScalar("gender", Hibernate.STRING).addScalar("nationality", Hibernate.STRING)
                    .addScalar("bloodtype", Hibernate.STRING).addScalar("constellation", Hibernate.STRING)
                    .addScalar("occupation", Hibernate.STRING).addScalar("height", Hibernate.STRING)
                    .addScalar("birthday", Hibernate.STRING).addScalar("alias", Hibernate.STRING)
                    .addScalar("homeplace", Hibernate.STRING).addScalar("description", Hibernate.STRING)
                    .addScalar("honor", Hibernate.STRING).addScalar("updateTime", Hibernate.TIMESTAMP)
                    .addScalar("index_flag", Hibernate.INTEGER)
                    .setResultTransformer(Transformers.aliasToBean(PersonExtend.class));
            query.setString(0, "%" + name + "%");
            query.setFirstResult(start);
            query.setMaxResults(limit);

            list = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return list;
    }

    @Override
    public int getPersonCountByName(String name, String[] nationalityArray) {
        StringBuilder hql = new StringBuilder("select count(*) from Person where name like ?");

        // nations
        if (nationalityArray != null && nationalityArray.length > 0) {
            StringBuilder sb = new StringBuilder();
            sb.append(" and nationality in (");
            for (int i = 0; i < nationalityArray.length; i++) {
                String nation = nationalityArray[i];
                sb.append("'");
                sb.append(nation);
                sb.append("'");
                if (i < nationalityArray.length - 1) {
                    sb.append(",");
                }
            }
            sb.append(")");
            hql.append(sb.toString());
        }

        Session session = null;
        try {
            session = getSession();
            Query query = session.createQuery(hql.toString());
            query.setString(0, "%" + name + "%");
            Object result = query.uniqueResult();

            return Integer.valueOf(result.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return 0;
    }

    private long getIdByPersonId(String personId) {
        String hql = "from Person where personId=?";
        Object[] paras = new Object[]{personId};
        List<?> list = this.getHibernateTemplate().find(hql, paras);
        return list == null || list.size() == 0 ? 0 : ((Person) list.get(0)).getId();
    }

    private boolean existExtendProprities(long id) {
        String hql = "from PersonExtend where id=?";
        Object[] paras = new Object[]{id};
        List<?> list = this.getHibernateTemplate().find(hql, paras);
        return list == null || list.size() == 0 ? false : true;
    }

    private void insertExtendProprities(long id) {
        String sql = "insert into personExtend(id) values(?)";
        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            query.setLong(0, id);

            query.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
    }
}